package cn.edu.zafu.bigdata.lb.studentdb;

import java.sql.*;
import java.util.Scanner;

public class StudentManagementSystemDB {
    private static final String DB_URL = "jdbc:mysql://192.168.169.76:3306/learnjava";
    private static final String USER = "learn";
    private static final String PASS = "learnpassword";
    private static Scanner scanner = new Scanner(System.in);

    public static void main(String[] args) {
        try {
            // Load the JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            // Connect to the database
            Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
            System.out.println("数据库连接成功！");

            while (true) {
                printMenu();
                int choice = scanner.nextInt();
                scanner.nextLine(); // consume newline
                switch (choice) {
                    case 1:
                        addStudent(conn);
                        break;
                    case 2:
                        searchStudent(conn);
                        break;
                    case 3:
                        updateStudent(conn);
                        break;
                    case 4:
                        deleteStudent(conn);
                        break;
                    case 5:
                        System.out.println("退出程序。");
                        conn.close();
                        return;
                    default:
                        System.out.println("无效的操作编号，请重新输入。");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void printMenu() {
        System.out.println("学生信息管理系统");
        System.out.println("1. 添加学生信息");
        System.out.println("2. 检索学生信息");
        System.out.println("3. 更新学生信息");
        System.out.println("4. 删除学生信息");
        System.out.println("5. 退出程序");
        System.out.print("请输入操作编号 (1-5): ");
    }

    private static void addStudent(Connection conn) throws SQLException {
        System.out.print("请输入学号: ");
        String id = scanner.nextLine();
        System.out.print("请输入姓名: ");
        String name = scanner.nextLine();
        System.out.print("请输入电话: ");
        String phone = scanner.nextLine();

        String sql = "INSERT INTO student119 (id, name, phone) VALUES (?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, id);
            pstmt.setString(2, name);
            pstmt.setString(3, phone);
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                System.out.println("学生信息添加成功。");
            } else {
                System.out.println("学生信息添加失败。");
            }
        } catch (SQLException e) {
            if (e.getErrorCode() == 1062) {
                System.out.println("学号已存在，请重新输入。");
            } else {
                throw e;
            }
        }
    }

    private static void searchStudent(Connection conn) throws SQLException {
        System.out.print("请输入要检索的学号: ");
        String id = scanner.nextLine();

        String sql = "SELECT * FROM student119 WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                System.out.println("学号: " + rs.getString("id"));
                System.out.println("姓名: " + rs.getString("name"));
                System.out.println("电话: " + rs.getString("phone"));
            } else {
                System.out.println("未找到该学生。");
            }
        }
    }

    private static void updateStudent(Connection conn) throws SQLException {
        System.out.print("请输入要更新的学号: ");
        String id = scanner.nextLine();
        System.out.print("请输入新的姓名: ");
        String name = scanner.nextLine();
        System.out.print("请输入新的电话: ");
        String phone = scanner.nextLine();

        String sql = "UPDATE student119 SET name = ?, phone = ? WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            pstmt.setString(2, phone);
            pstmt.setString(3, id);
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                System.out.println("学生信息更新成功。");
            } else {
                System.out.println("未找到该学生。");
            }
        }
    }

    private static void deleteStudent(Connection conn) throws SQLException {
        System.out.print("请输入要删除的学号: ");
        String id = scanner.nextLine();

        String sql = "DELETE FROM student119 WHERE id = ?";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, id);
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                System.out.println("学生信息删除成功。");
            } else {
                System.out.println("未找到该学生。");
            }
        }
    }
}
